<?php

class ProductTable extends Doctrine_Table {

    const TYPE_MAIN = 1; # бараа
    const TYPE_SERVICE = 2; # үйлчилгээ
    const ACTIVE = 1;
    const PASSIVE = 2;

    public static function getInstance() {
        return Doctrine_Core::getTable('Product');
    }

    public static function retrieveByPK($id, $isActive = null) {
        $id = (int) $id;
        $q = self::getInstance()->createQuery()
                ->where("id = ?", $id);

        if ($isActive) {
            $q->addWhere('is_active = ?', $isActive);
        }

        return $q->fetchOne();
    }

    public static function getTypeForSelect() {
        return array(self::TYPE_MAIN => 'Бүтээгдэхүүн', self::TYPE_SERVICE => 'Үйлчилгээ');
    }

    public static function getForSelect($categoryId = 0, $withPrice = 0) {
        $where = array();
        $where[] = "p.is_active = " . self::ACTIVE;

        if ($categoryId) {
            $where[] = "p.category_id = $categoryId";
        }

        $select = "SELECT 
                    p.id,
                    p.name";
        if ($withPrice) {
            $select .= ", p.price";
        }

        $sql = "$select
                FROM product AS p
                WHERE " . implode(' AND ', $where) . "
                ORDER BY p.name ASC";

        $pdo = Doctrine_Manager::connection()->getDbh();
        $rows = $pdo->query($sql)->fetchAll();

        $arr = array();
        foreach ($rows AS $row) {
            if ($withPrice) {
                $arr['0' . $row['id']]['name'] = $row['name'];
                $arr['0' . $row['id']]['price'] = $row['price'];
            } else {
                $arr['0' . $row['id']] = $row['name'];
            }
        }

        return $arr;
    }

    public static function getList($productId = 0, $categoryId = 0, $manufacturerId = 0, $isActive = 1, $sorting = 'p.id ASC', $start = 0, $length = 30, $price = '', $priceSale = 0) {
        $productId = (int) $productId;
        $categoryId = (int) $categoryId;
        $manufacturerId = (int) $manufacturerId;

        $where = array();

        if ($productId) {
            $where[] = "p.id = $productId";
        }
        if ($categoryId) {
            $where[] = "p.category_id = $categoryId";
        }
        if ($manufacturerId) {
            $where[] = "p.manufacturer_id = $manufacturerId";
        }
        if ($price != '' && is_numeric($price)) {
            $where[] = "p.price = $price";
        }
        if ($priceSale != '' && is_numeric($priceSale)) {
            $where[] = "p.price_sale = $priceSale";
        }
        if ($isActive) {
            $where[] = "p.is_active = $isActive";
        }
//        $where[] = "((pp.is_active = " . ProductPhotoTable::ACTIVE . " OR pp.id IS NULL) OR (ppm.is_active = " . ProductPhotoTable::ACTIVE . " OR ppm.id IS NULL))";

        $where = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';

        $select = "p.id,
                    c.name AS cat_name,
                    m.name AS man_name,
                    p.name,
                    p.type,
                    IF(LENGTH(p.intro_description), CONCAT(SUBSTRING_INDEX(p.intro_description, ' ', 5), '...'), '') AS 'intro_description',
                    p.price,
                    p.price_sale,
                    IF(p.sale_begin OR p.sale_end, CONCAT(p.sale_begin, ' - ', p.sale_end), '') AS sale_date,
                    p.code,
                    p.product_date,
                    p.is_active,
                    p.created_at,
                    pp.image_name AS image_name,
                    ppm.image_name AS main_image_name";
        $order = "ORDER BY $sorting";
        $limit = "LIMIT $start, $length";

        $sql = "SELECT $select
                FROM `product` p
                LEFT JOIN `category` c ON c.id = p.category_id
                LEFT JOIN `manufacturer` m ON m.id = p.manufacturer_id
                LEFT JOIN `product_photo` pp ON pp.product_id = p.id
                LEFT JOIN `product_photo` ppm ON ppm.product_id = p.id AND ppm.type = " . ProductPhotoTable::TYPE_MAIN . "
                $where
                GROUP BY p.id
                $order
                $limit";

        $sqlCount = "SELECT COUNT(t.pid) AS numrow FROM
                    (SELECT p.id AS pid
                    FROM `product` p
                    LEFT JOIN `category` c ON c.id = p.category_id
                    LEFT JOIN `manufacturer` m ON m.id = p.manufacturer_id
                    LEFT JOIN `product_photo` pp ON pp.product_id = p.id
                    LEFT JOIN `product_photo` ppm ON ppm.product_id = p.id AND ppm.type = " . ProductPhotoTable::TYPE_MAIN . "
                    $where
                    GROUP BY p.id) AS t";

        $pdo = Doctrine_Manager::connection()->getDbh();
        $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $numrows = $pdo->query($sqlCount)->fetchAll(PDO::FETCH_ASSOC);

        return array('numrows' => $numrows[0]['numrow'], 'rows' => $rows);
    }

    public static function getListForSync($categoryId = 0, $manufacturerId = 0, $productId = null, $syncDate = null) {
        $categoryId = (int) $categoryId;
        $manufacturerId = (int) $manufacturerId;

        $where = array();
        if ($categoryId) {
            $where[] = "p.category_id = $categoryId";
        }
        if ($manufacturerId) {
            $where[] = "p.manufacturer_id = $manufacturerId";
        }
        if ($productId) {
            $where[] = "p.id = $productId";
        }
        if (AppTools::validateDate($syncDate, 'Y-m-d H:i:s')) {
//            $where[] = "( p.updated_at >= '$syncDate' OR pp.updated_at >= '$syncDate')";
            $where[] = "(" . AppTools::convertTimezone('p.updated_at') . " >= '$syncDate' OR " . AppTools::convertTimezone('pp.updated_at') . " >= '$syncDate')";
        }

        $where = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';

        $sql = "SELECT p.id,
                        p.category_id,
                        p.manufacturer_id,
                        p.name,
                        p.type,
                        p.intro_description,
                        p.description,
                        p.price,
                        p.price_sale,
                        p.sale_begin,
                        p.sale_end,
                        p.code,
                        p.product_date,
                        p.is_active AS p_is_active,
                        pp.id AS image_id,
                        pp.image_name,
                        pp.type AS image_type,
                        pp.id AS image_id,
                        pp.is_active AS pp_is_active,
                        c.is_active AS c_is_active
                FROM `product` p
                LEFT JOIN `product_photo` pp ON p.id = pp.product_id
                LEFT JOIN `category` c ON p.category_id = c.id
                $where
                ORDER BY p.id";

        $pdo = Doctrine_Manager::connection()->getDbh();
        $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        return $rows;
    }

    public static function convertProductsToArray($rows) {
        $arr = array();
        $curId = 0;
        $temp = array();

        foreach ($rows as $idx => $row) {
            #if the product is inactive
            if ($row['p_is_active'] == self::PASSIVE || $row['c_is_active'] == CategoryTable::PASSIVE) {
                foreach ($arr as $prod) {
                    if ($prod['id'] == $row['id']) {
                        continue 2;
                    }
                }
                $arr[] = array('id' => $row['id'], 'deleted' => 1);
                continue;
            }
            if ($curId != $row['id']) {
                $curId = $row['id'];
                if (count($temp)) {
                    $arr[] = $temp;
                }
                $temp = array();
            }
            foreach ($row as $col => $val) {
                if (!in_array($col, array('p_is_active', 'image_id', 'image_name', 'image_type', 'image_id', 'pp_is_active'))) {
                    $temp[$col] = $val;
                } else {
                    if (in_array($col, array('image_id', 'image_name', 'image_type'))) {
                        #if the product photo is inactive
                        if ($row['image_id'] && $row['pp_is_active'] == ProductPhotoTable::PASSIVE) {
                            $temp['photos'][$row['image_id']] = array('image_id' => $row['image_id'], 'deleted' => 1);
                            continue;
                        }
                        if ($val != '') {
                            $temp['photos'][$row['image_id']][$col] = $val;
                        }
                    }
                }
            }
            unset($rows[$idx]);
        }
        if (count($temp)) {
            $arr[] = $temp;
        }
        #cleaning photo_id from array index
        foreach ($arr as $i => $v) {
            if (!isset($v['photos']) || $v['photos'] == null) {
                $arr[$i]['photos'] = array();
            } else {
                $arr[$i]['photos'] = array_values($v['photos']);
            }
        }
        return $arr;
    }

}
